package com.idrem.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBManager extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "idremdb";

	private static final int DATABASE_VERSION = 1;

	/* 
	 * Table for products.
	 */
	public static final String DB_PRODUCT_TABLE = "product";
	public static final String PRODUCT_PNO = "pno";
	public static final String PRODUCT_PARTNO = "partno";
	public static final String PRODUCT_NPARTNO = "npartno";
	public static final String PRODUCT_PTYPE = "ptype";
	public static final String PRODUCT_PNAME = "pname";
	public static final String PRODUCT_BRAND = "brandname";
	public static final String PRODUCT_MODEL = "pmodel";
	public static final String PRODUCT_PUNITP = "punitp";
	public static final String PRODUCT_PUNITS = "punits";
	public static final String PRODUCT_UNITSTR = "unitstr";
	public static final String PRODUCT_RATESTR = "ratestr";
	public static final String PRODUCT_PRATE = "prate";
	
	
	
	private static final String PRODUCT_TABLE = "create table " + DB_PRODUCT_TABLE + " (_id integer primary key autoincrement, "

		+ PRODUCT_PNO + " text not null," + PRODUCT_PARTNO + " text not null," + PRODUCT_NPARTNO + " text not null," + PRODUCT_PTYPE + " text,"
		
		+ PRODUCT_PNAME + " text," + PRODUCT_BRAND + " text," + PRODUCT_MODEL + " text," + PRODUCT_PUNITP + " text,"
		
		+ PRODUCT_PUNITS + " text," + PRODUCT_UNITSTR + " text," + PRODUCT_RATESTR + " text," + PRODUCT_PRATE + " text);";
		
	
	/*
	 * Table for shops.
	 */
	public static final String DB_SHOP_TABLE = "shop";
	public static final String SHOP_CNO = "cno";
	public static final String SHOP_CCODE = "ccode";
	public static final String SHOP_CHANNEL = "channel";
	public static final String SHOP_NAME = "cname";
	public static final String SHOP_NAMES = "cnames";
	public static final String SHOP_ADDRESS = "address";
	public static final String SHOP_LATITUDE = "latitude";
	public static final String SHOP_LONGITUDE = "longitude";
	public static final String SHOP_CONTACTOR = "contactor";
	public static final String SHOP_PHONE = "phone";
	public static final String SHOP_AREA = "area";
	public static final String SHOP_CREATED_DATE = "created_date";
	
	private static final String SHOP_TABLE = "create table " + DB_SHOP_TABLE + " (_id integer primary key autoincrement, "
		+ SHOP_CNO + " text not null," + SHOP_CCODE + " text not null," + SHOP_CHANNEL + " text," + SHOP_NAME + " text,"
	+ SHOP_NAMES + " text," + SHOP_ADDRESS + " text," + SHOP_LATITUDE + " text," + SHOP_LONGITUDE + " text,"
	+ SHOP_CONTACTOR + " text," + SHOP_PHONE + " text," + SHOP_AREA + " text," + SHOP_CREATED_DATE + " text);";
	
	
	/* 
	 * Table for constant.
	 */
	public static final String DB_CONSTANT_TABLE = "constant";
	public static final String CONSTANT_TYPE = "type";
	public static final String CONSTANT_LABEL = "label";
	public static final String CONSTANT_VALUE = "value";
	
	private static final String CONSTANT_TABLE = "create table " + DB_CONSTANT_TABLE + " (_id integer primary key autoincrement, "
			+ CONSTANT_TYPE + " text not null," + CONSTANT_LABEL + " text not null," + CONSTANT_VALUE + " text);";
	
	
	
	/* 
	 * Table for kpi.
	 */
	public static final String DB_KPI_TABLE = "kpi";
	public static final String KPI_DATE = "date";
	public static final String KPI_CNO = "cno";
	public static final String KPI_BALANCE = "balance";
	public static final String KPI_TARGET = "target";
	public static final String KPI_IND = "indication";
	
	private static final String KPI_TABLE = "create table " + DB_KPI_TABLE + " (_id integer primary key autoincrement, "
			+ KPI_DATE + " text not null," + KPI_CNO + " text not null," + KPI_BALANCE + " text, "
	+ KPI_TARGET + " text," + KPI_IND + " text);";
	
	/* 
	 * Table for pinfo. 
	 */
	public static final String DB_PINFO_TABLE = "pinfo";
	public static final String PINFO_PNO = "pno";
	public static final String PINFO_CCODE = "ccode";
	public static final String PINFO_PRICEP = "pricep";
	public static final String PINFO_PRICES = "prices"; 
	public static final String PINFO_STOCKQTY ="stockqty"; 
	public static final String PINFO_QTY = "qty"; 
	public static final String PINFO_CX = "cx";
	public static final String PINFO_XP = "xp";
	public static final String PINFO_LASTCOUNT = "last3count";
	
	private static final String PINFO_TABLE = "CREATE TABLE " + DB_PINFO_TABLE + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
		+ PINFO_PNO + " TEXT not null, " + PINFO_CCODE + " TEXT not null, " + PINFO_PRICEP + " TEXT, " + PINFO_PRICES + " TEXT, " + PINFO_STOCKQTY + " TEXT, " 
		+ PINFO_QTY + " TEXT, " + PINFO_CX + " TEXT, " + PINFO_XP + " TEXT, "  + PINFO_LASTCOUNT + " TEXT);";
	
	/* 
	 * Table for task. 
	 */
	public static final String DB_TASK_TABLE = "task";
	public static final String TASK_DATE = "date";
	public static final String TASK_CNO = "cno";
	
	
	private static final String TASK_TABLE = "CREATE TABLE " + DB_TASK_TABLE + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "  
		+ TASK_DATE + " TEXT not null, " + TASK_CNO  + " TEXT not null);";
	
	
	/**
	 * Constructor.
	 * @param context
	 */
	public DBManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
		db.execSQL(PRODUCT_TABLE);
		db.execSQL(KPI_TABLE);
		db.execSQL(SHOP_TABLE);
		db.execSQL(CONSTANT_TABLE);
		db.execSQL(PINFO_TABLE);
		db.execSQL(TASK_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + DB_PRODUCT_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_KPI_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_SHOP_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TASK_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_CONSTANT_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_PINFO_TABLE);
		onCreate(db);
	}

}
